/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dalobject;

import chat.TextChatMessage;
import dalInterface.DALMessage;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.logging.Level;
import java.util.logging.Logger;
import utils.DataTable;

/**
 *
 * @author salm
 */
public class DALMessageImpl implements DALMessage
{
    private final Connection conn;
    
    public DALMessageImpl(Connection sqlConnection)
    {
        this.conn = sqlConnection;
    }
    
    @Override
    public void addNewMessage(TextChatMessage msg) throws RemoteException {
        synchronized (conn)
        {
            try {
                    java.util.Date msgDay = (java.util.Date) Calendar.getInstance().getTime();
                    java.sql.Timestamp sqlDate = new java.sql.Timestamp(msgDay.getTime());

                    StringBuilder sb = new StringBuilder();
                    sb.append("INSERT INTO CHATMESSAGE");
                    sb.append("(Sender, Receiver, Content, SendTime) ");
                    sb.append("VALUES ( ");
                    sb.append("?, ?, ?, ?");
                    sb.append(")");

                    PreparedStatement stmt = conn.prepareStatement(sb.toString());
                    stmt.setString(1, msg.getSenderID());
                    stmt.setString(2, msg.getReceiverID());
                    stmt.setString(3, msg.getMessageContent());
                    stmt.setTimestamp(4, sqlDate);

                    stmt.executeUpdate(); // optionally check the return value of this call
            }
            catch (SQLException ex) {
                Logger.getLogger(DALAccountImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    @Override
    public DataTable getAllMessagesFrom(String sender) throws RemoteException
    {
        synchronized (conn)
        {
            try {
                Statement st = conn.createStatement();
                String queryString = String.format("SELECT * from CHATMESSAGE where "
                        + "Sender=\'%s\' ORDER BY SendTime ASC", sender);

                return DataTable.createDataTable(st.executeQuery(queryString),
                        "Sender", "Receiver", "SendTime", "Content");
            } catch (SQLException ex) {
                Logger.getLogger(DALAccountImpl.class.getName()).log(Level.SEVERE, null, ex);
            }

            return null;
        }
    }

    @Override
    public DataTable getAllMessageTo(String receiver) throws RemoteException
    {
        synchronized (conn)
        {
            try {
                Statement st = conn.createStatement();
                String queryString = String.format("SELECT * from CHATMESSAGE where Receiver=\'%s\' ORDER BY SendTime ASC", receiver);

                return DataTable.createDataTable(st.executeQuery(queryString), "Sender", "Receiver", "SendTime", "Content");
            } catch (SQLException ex) {
                Logger.getLogger(DALAccountImpl.class.getName()).log(Level.SEVERE, null, ex);
            }

            return null;
        }
    }

    @Override
    public DataTable getConversation(String user1, String user2) throws RemoteException
    {
        synchronized (conn)
        {
            try {
                Statement st = conn.createStatement();
                String queryString = String.format("SELECT * from CHATMESSAGE where"
                        + "(Sender = \'%s\' AND Receiver=\'%s\') OR"
                        + "(Receiver = \'%s\' AND Sender=\'%s\')"
                        + " ORDER BY SendTime ASC", user1, user2, user1, user2);

                return DataTable.createDataTable(st.executeQuery(queryString),
                        "Sender", "Receiver", "SendTime", "Content");
            } catch (SQLException ex) {
                Logger.getLogger(DALAccountImpl.class.getName()).log(Level.SEVERE, null, ex);
            }

            return null;
        }
    }

    @Override
    public DataTable getLimitedConversation(String user1, String user2, int nMessages) throws RemoteException
    {
        synchronized (conn)
        {
        try {
                Statement st = conn.createStatement();
                String queryString = String.format("SELECT * from CHATMESSAGE where"
                        + " (Sender = \'%s\' AND Receiver=\'%s\') OR"
                        + " (Receiver = \'%s\' AND Sender=\'%s\')"
                        + " LIMIT %d"
                        + " ORDER BY SendTime ASC", user1, user2, user1, user2, nMessages);

                return DataTable.createDataTable(st.executeQuery(queryString),
                        "Sender", "Receiver", "SendTime", "Content");
            } catch (SQLException ex) {
                Logger.getLogger(DALAccountImpl.class.getName()).log(Level.SEVERE, null, ex);
            }

            return null;
        }
    }

    @Override
    public DataTable getLimitedMessagesFrom(String sender, int nMessages) throws RemoteException
    {
        synchronized (conn)
        {
            try {
                Statement st = conn.createStatement();
                String queryString = String.format("SELECT TOP %d * from CHATMESSAGE where "
                        + "Sender=\'%s\' ORDER BY SendTime ASC", nMessages, sender);

                return DataTable.createDataTable(st.executeQuery(queryString),
                        "Sender", "Receiver", "SendTime", "Content");
            } catch (SQLException ex) {
                Logger.getLogger(DALAccountImpl.class.getName()).log(Level.SEVERE, null, ex);
            }

            return null;
        }
    }

    @Override
    public DataTable getLimitedMessageTo(String receiver, int nMessages) throws RemoteException
    {
        synchronized (conn)
        {
            try {
                Statement st = conn.createStatement();
                String queryString = String.format("SELECT * from CHATMESSAGE where "
                        + "Receiver=\'%s\' LIMIT %d ORDER BY SendTime ASC", receiver, 
                        nMessages);

                return DataTable.createDataTable(st.executeQuery(queryString), "Sender", "Receiver", "SendTime", "Content");
            } catch (SQLException ex) {
                Logger.getLogger(DALAccountImpl.class.getName()).log(Level.SEVERE, null, ex);
            }

            return null;
        }
    }
}